1. Field of the Invention
The invention relates generally to relational database systems and more specifically to techniques for simplifying the redefinition of a group of related objects in the database system.
2. Description of Related Art
Database systems are systems which store data and permit users of the systems to access items of the data by means of queries which describe the data in terms of the structure given the data by the database system. A common kind of database system is a relational database system. In such systems, the data is organized as a set of tables. A relational database table has a fixed number of columns and a variable number of rows. Each row has afield corresponding to each column, and the field contains a value. Queries on relational databases specify the data to be accessed in terms of the table or tables that contain it, columns in the table, and values of fields in some of the specified columns. For example, a simple table employees might look like this:
emp_noemp_name001Jones002Smith003Andrews004Todd
The table has two columns, named emp_no, whose fields contain employee numbers, and emp_name, whose fields contain employee names, and four rows. A query that returned the name of the employee with the employee number “002” would look like this in the standard SQL language used with relational database systems:                SELECT emp_name FROM employees WHERE emp_no=002;        
When the database system executes the query, it finds the row in the table employees whose field in the column emp_no has the value “002” and returns the value of the field in the row belonging to the column emp_name, or “Smith”.
FIG. 1 shows the portions of a typical relational database system 101 that are relevant to the present discussion. The main components of system 101 are a processor, a memory 103 which contains programs 105 being executed by the processor and data 113 involved in the program executions, and persistent storage 123 for the database system's tables and other objects. Processor 121 may further receive inputs from input devices such as a keyboard and/or pointing device and produce outputs to a display device such as a CRT, as shown at 122, and may also receive inputs from and provide outputs to one or more networks, containing other processors, as shown at 124.
When system 101 is operating, programs 105 in memory 103 include an operating system 107, a relational database system program 109, and application programs 111 that employ the services provided both by operating system 107 and database program 109. Correspondingly, data 113 in memory 103 includes data for the application programs, data for the operating system, and data for the database system. Operation typically involves an application program 111, which provides a query to relational database program 109. Database program 109 executes the query on the tables in persistent storage 123 and provides the result to application program 111. Both RDB program 109 and application program 111 use the services provided by operating system 107, and execution of the application program or the database RDB program may involve inputs from and outputs to I/O devices and the network.
Continuing in more detail, persistent storage 123 contains two classes of objects: DB system objects, which are objects, including tables, that database system 101 uses to manage and operate the database system, and user objects 129, which contain tables and other objects defined by users of the database system. In the present context, the only system objects which are important are those belonging to data dictionary 127, which contains definitions of all of the objects in the database system.
User tables include base tables 131, views 118, and materialized views 141. Base tables 131 are the tables that are the actual sources of the data returned by a query. Views are tables which do not exist in their own rights in persistent storage 123, but are instead created using data from other tables. Data dictionary 227 contains definitions of base tables, of tables defined in terms of the base tables, and definitions of other objects that are defined for the tables. These other objects include indexes, which speed up access to the data contained in a column of a table, triggers, which define actions to be taken upon occurrence of events concerning the table, and constraints, i.e. rules about the values that must be in the fields.
A view is defined in the data dictionary by a query on other tables. The other tables may also be views, but the data must ultimately come from base tables. View 118 contains four columns and three rows. The data in columns 1 and 2 comes from columns 1 and 2 of base table 131(a); the data in columns 3 and 4 comes from columns 3 and 4 of base table 131(b); the query that defines view 118 has selected the fields of columns 1 and 2 of rows 3–5 of table 131(a) and the fields of columns 3 and 4 of rows 2, 6, and 8 of table 131(b). When relational database system 101 executes a query on a view 118, it must first make the view, which requires running the query which defines the view; consequently, if the view is frequently queried, the view may be made into a materialized view 141, which is a copy of the view which has been stored in persistent storage 123. Data dictionary 127 keeps track of the fact that there is a materialized view 141 corresponding to view 118, and database system 101 redirects a query of view 118 to materialized view 141.
The standard SQL language used to describe operations on relational databases has two major subdivisions: the data manipulation language, or DML, which is used to read and modify rows and individual data fields in the database's tables, and the data definition language, or DDL, which is used to define and redefine the tables themselves. The query discussed above is an example of a DML statement; a DDL statement that redefines the employees table by adding a column comments for comments looks like this:                ALTER TABLE employees ADD (comments VARCHAR2(100));        
For details on the standard SQL language, see Oracle8i SQL Reference, Release 3 (8.1.7), Part Number A85397-01, available in March, 2002 from the Oracle Corporation website http://otn.oracle.com. While DML statements that write to a view or materialized view write to the base tables underlying the view or materialized view, DDL statements cannot be used with a view or a materialized view. Thus, if one wants to redefine a view or a materialized view, one must use DDL to add the column to a base table which underlies the view or materialized view is based and then to redefine any other objects in the database system that are affected by the change in the base table. Of course, the DDL can be used this way only if one understands how the view is related to the other objects.
When databases were relatively simple and the people who redefined views were the same people who had set up the objects that needed redefining if the view was redefined, the need to understand the base tables and other objects related to a view in order to redefine the view was not a serious problem. Now it has become one. The relationships between views and the underlying base tables have become more complex and in many cases, the people who are making and using the views are not the people who originally designed the database.
One example of the increasingly complex relationships between views and the underlying tables and between the users of the database system and the data is data mining. Data mining attempts to extract new kinds of information from old databases, for example, marketing information from a telephone company's billing database. Since the database was originally set up for billing purposes, not marketing research purposes, the most efficient way to use it for marketing research purposes is to define a set of views which extract the marketing information from the billing database. These views are defined by the data miners, who are generally not the people who designed the original billing database. Moreover, data miners are continually redefining the views they use. As things presently stand, they cannot do so without intimate knowledge of the underlying billing database.
Another example is versioned databases. A versioned database is one in which different versions of the database exist simultaneously. There is a view corresponding to each version of the database, and what users of the versioned database see is these views, rather than the underlying base tables. For details on a commercially-available versioned database, see Oracle9i Application Developer's Guide—Workspace Manager, Release 1 (9.0.1), Part Number A88806-01, available in March, 2002 at the above-mentioned Web site. Versioned databases are typically used in research and development situations: when a new line of research which will affect an existing database begins, a new version of the database is associated with the line of work, and the results of the research are incorporated into the new version of the database. When the work is to the point where it can be incorporated into the existing database, the new version is merged into the existing database. Again, the way in which versioned databases are used often makes redefinition of the views necessary.
To the extent that the prior art has dealt at all with the problem of making changes in the definitions of views that are reflected in the database objects that underlie and are related to the views, it has done so by means of system-specific procedural interfaces. In the context of relational database systems, the use of procedural interfaces to redefine views has significant costs.                In relational database systems generally, objects are defined and redefined using DDL, and most people who work with relational database systems know and are comfortable with DDL. That is not the case with the procedural interfaces.        Relational database systems come with powerful and efficient tools for writing and processing DDL definitions of database objects. These tools are not available for the procedural interfaces.        
What is needed is a technique that permits a user of a relational database system to use DDL to redefine a view without having to understand and redefine the objects that are related to the view.